﻿using System;
using System.Data;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using StockTrader.DAL.IDAL;
using StockTrader.DAL.MySQLHelper;
using StockTrader.BusinessService.DataContract;
using StockTrader.BusinessService.DataContract.Structure;
using StockTrader.DAL.IDAL.LoanMonitor;
using StockTrader.BusinessService.DataContract.Mortgate;

namespace StockTrader.DAL.MySQL
{
    public class Mortgage : IMortgage
    {
        private MySqlConnection _internalConnection;
        private MySqlTransaction _internalADOTransaction = null;

        public void BeginADOTransaction()
        {
            if (_internalConnection.State != System.Data.ConnectionState.Open)
                _internalConnection.Open();
            _internalADOTransaction = _internalConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        }
        public void RollBackTransaction()
        {
            _internalADOTransaction.Rollback();
            _internalADOTransaction = null;
        }
        public void CommitADOTransaction()
        {
            _internalADOTransaction.Commit();
            _internalADOTransaction = null;
        }
        public void Open(string connString)
        {
            if (_internalConnection == null)
                _internalConnection = new MySqlConnection(connString);
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
        }
        public void Close()
        {
            if (_internalConnection != null && _internalConnection.State != System.Data.ConnectionState.Closed)
                _internalConnection.Close();
        }

        #region SQL string
        private const string IMPORT_LOANRATE = "INSERT INTO LOANRATEHISTORY(TRANSDATE, SYMBOL, RATE) VALUES (@TRANSDATE, @SYMBOL, @RATE) ON DUPLICATE KEY UPDATE TRANSDATE= @TRANSDATE, SYMBOL= @SYMBOL, RATE= @RATE;";

        private const string GET_LOANRATE = "SELECT RATE FROM LOANRATEHISTORY WHERE (SYMBOL=@SYMBOL) AND DATEDIFF(TRANSDATE,@TRANSDATE)<=0 ORDER BY TRANSDATE DESC LIMIT 1;";

        private const string DELETE_LOANRATE = "DELETE FROM LOANRATEHISTORY WHERE DATEDIFF(TRANSDATE,@TRANSDATE)=0;";

        private const string INSERT_MORTGATE_TRANS = "INSERT INTO M_TRANS(type, debit, credit, transdate, contractno, accountno, code) VALUES (@type, @debit, @credit, @transdate, @contractno, @accountno, @code)";

        private const string DELETE_MORTGATE_TRANS_BYCONTRACT = "DELETE FROM M_TRANS WHERE contractno=@contractno;";

        private const string GET_MORTGAGE_TRANS_BYCODE = "SELECT * FROM M_TRANS WHERE Code=@contractno;";

        private const string SEARCH_TRANS = "SELECT * FROM M_TRANS WHERE 1=1 ";

        private const string INSERT_CUSTOMER = "INSERT INTO M_CUSTOMER(accountno, name, idcardno, idcarddate, idcardissuer, phonemobile, phonefix, email, addresscontact, addressresident) VALUES (@accountno, @name, @idcardno, @idcarddate, @idcardissuer, @phonemobile, @phonefix, @email, @addresscontact, @addressresident) ON DUPLICATE KEY UPDATE accountno=@accountno, name=@name, idcardno=@idcardno, idcarddate=@idcarddate, idcardissuer=@idcardissuer, phonemobile=@phonemobile, phonefix=@phonefix, email=@email, addresscontact=@addresscontact, addressresident=@addressresident;";

        private const string GET_CUSTOMER_BYID = "SELECT * FROM M_CUSTOMER WHERE IDCARDNO = @IDCARDNO;";

        private const string GET_CUSTOMERS = "SELECT * FROM M_CUSTOMER";

        private const string GET_CUSTOMERID_BYACCOUNTNO = "SELECT * FROM m_customeraccount WHERE accountno = @accountno;";


        private const string GET_CUSTOMER_BYNAME = "SELECT * FROM M_CUSTOMER WHERE NAME LIKE '%@NAME%';";

        private const string DELETE_CUSTOMER_BYID = "DELETE FROM M_CUSTOMER WHERE IDCARDNO=@IDCARDNO;";

        private const string INSERT_FEE = "INSERT INTO M_FEE(startdate, enddate, name, type, FeeValue, CustomerID, ContractNo, AccountNo) VALUES (@startdate, @enddate, @name, @type, @FeeValue, @customerid, @contractno, @AccountNo) ON DUPLICATE KEY UPDATE startdate=@startdate, enddate=@enddate, name=@name, type=@type, FeeValue=@FeeValue, customerid=@customerid, contractno=@contractno, AccountNo=@AccountNo;";

        private const string GET_FEE_BYCODE = "SELECT * FROM M_FEE WHERE ID=@ID";

        private const string GET_FEE = "SELECT * FROM M_FEE WHERE TYPE=@TYPE AND CUSTOMERID=@CUSTOMERID AND CONTRACTNO=@CONTRACTNO AND ACCOUNTNO=@ACCOUNTNO;";

        private const string GET_FEE_CURRENT = "SELECT * FROM M_FEE WHERE TYPE=@TYPE AND CUSTOMERID=@CUSTOMERID AND CONTRACTNO=@CONTRACTNO AND STARTDATE<=CURRENT_TIMESTAMP and  ENDDATE>=CURRENT_TIMESTAMP;";

        private const string INSERT_CONTRACT = "INSERT INTO M_CONTRACT(contractno, startdate, enddate, customerid, fee, penaltyfee, processfee, contracttype, status, contractvalue, accountno, original) VALUES (@contractno, @startdate, @enddate, @customerid, @fee, @penaltyfee, @processfee, @contracttype, @status, @contractvalue, @accountno, @original) ON DUPLICATE KEY UPDATE contractno=@contractno, startdate=@startdate, enddate=@enddate, customerid=@customerid, fee=@fee, penaltyfee=@penaltyfee, processfee=@processfee, contracttype=@contracttype, status=@status, contractvalue=@contractvalue, accountno=@accountno, original=@original ;";

        private const string GET_CONTRACT_BYCONTRACTNO = "SELECT * FROM M_CONTRACT WHERE contractno=@contractno;";

        private const string GET_CONTRACT_BYCUSTOMERID = "SELECT * FROM M_CONTRACT WHERE CUSTOMERID=@customerid;";

        private const string GET_CONTRACT_BYSTARTDATE = "SELECT * FROM M_CONTRACT WHERE STARTDATE BETWEEN (@FROM, @TO);";

        private const string GET_CONTRACT_BYENDDATE = "SELECT * FROM M_CONTRACT WHERE ENDDATE BETWEEN (@FROM, @TO);";

        private const string GET_CONTRACT_BYVALUE = "SELECT * FROM M_CONTRACT WHERE CONTRACTVALUE BETWEEN (@FROM, @TO);";

        private const string SEARCH_CONTRACT = "SELECT * FROM M_CONTRACT WHERE 1=1 ";

        private const string INSERT_CUSTOMERACCOUNT = "INSERT INTO M_CUSTOMERACCOUNT(customerid, accountno) VALUES (@customerid, @accountno) ON DUPLICATE KEY UPDATE customerid=@customerid, accountno=@customerid;";

        private const string GET_CUSTOMERACCOUNT_BYCUSTOMERID = "SELECT * FROM M_CUSTOMERACCOUNT WHERE CUSTOMERID=@CUSTOMERID;";

        private const string DELETE_CUSTOMERACCOUNT_BYCUSTOMERID = "DELETE FROM M_CUSTOMERACCOUNT WHERE CUSTOMERID=@CUSTOMERID;";

        private const string GET_BLOCKING_BYCODE = "SELECT sum(DEBIT) AS QTTY FROM M_TRANS WHERE ACCOUNTNO=@ACCOUNTNO AND CODE=@CODE AND TYPE=1";

        private const string GET_BLOCKING_BYCODEANDCONTRACTNO = "select sum(credit) as QTTY from m_trans where accountno=@ACCOUNTNO code =@CODE AND TYPE=1 AND contractno=@CONTRACTNO";

        private const string GET_CONTRACT_BYACCOUNTNO = "SELECT * FROM M_CONTRACT WHERE CUSTOMERID=@CUSTOMERID;";

        private const string INSERT_ACCUMULATELOAN = "INSERT INTO M_ACCUMULATE(ACCOUNTNO, ACCUMULATEVALUE, LASTUPDATE) VALUES (@ACCOUNTNO,@ACCUMULATEVALUE, @LASTUPDATE) ON DUPLICATE KEY UPDATE ACCOUNTNO=@ACCOUNTNO,ACCUMULATEVALUE=@ACCUMULATEVALUE, LASTUPDATE=@LASTUPDATE;";

        private const string GET_ACCUMULATEVALUE_BYACCOUNTNO = "SELECT * FROM M_ACCUMULATE WHERE ACCOUNTNO=@ACCOUNTNO;";

        #endregion

        #region Params
        private const string PARM_TRANSDATE = "@TRANSDATE";
        private const string PARM_SYMBOL = "@SYMBOL";
        private const string PARM_RATE = "@RATE";
        private const string PARM_TYPE = "@TYPE";
        private const string PARM_DEBIT = "@DEBIT";
        private const string PARM_CREDIT = "@CREDIT";
        private const string PARM_DATE = "@DATE";
        private const string PARM_CONTRACTVALUE = "@CONTRACTVALUE";
        private const string PARM_CONTRACTNO = "@CONTRACTNO";
        private const string PARM_ACCOUNTNO = "@ACCOUNTNO";
        private const string PARM_CODE = "@CODE";
        private const string PARM_STARTDATE = "@STARTDATE";
        private const string PARM_ENDDATE = "@ENDDATE";
        private const string PARM_CUSTOMERID = "@CUSTOMERID";
        private const string PARM_FROM = "@FROM";
        private const string PARM_TO = "@TO";
        private const string PARM_NAME = "@NAME";
        private const string PARM_GENDER = "@GENDER";
        private const string PARM_IDCARDNO = "@IDCARDNO";
        private const string PARM_IDCARDDATE = "@IDCARDDATE";
        private const string PARM_IDISSUER = "@IDCARDISSUER";
        private const string PARM_PHONEMOBILE = "@PHONEMOBILE";
        private const string PARM_PHONEFIX = "@PHONEFIX";
        private const string PARM_EMAIL = "@EMAIL";
        private const string PARM_ADDRESSCONTACT = "@ADDRESSCONTACT";
        private const string PARM_ADDRESSRESIDENT = "@ADDRESSRESIDENT";
        private const string PARM_FEEVALUE = "@FEEVALUE";
        private const string PARM_FEE = "@FEE";
        private const string PARM_PENALTYFEE = "@PENALTYFEE";
        private const string PARM_PROCESSFEE = "@PROCESSFEE";
        private const string PARM_CONTRACTTYPE = "@CONTRACTTYPE";
        private const string PARM_ORIGINAL = "@ORIGINAL";
        private const string PARM_STATUS = "@STATUS";
        #endregion

        #region ILoanRate Members


        public void InportLoadRate(LoanRateData item)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                        new MySqlParameter(PARM_TRANSDATE, MySqlDbType.DateTime),
                                        new MySqlParameter(PARM_SYMBOL, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_RATE, MySqlDbType.Decimal)};
                parms[0].Value = item.TransDate;
                parms[1].Value = item.Symbol;
                parms[2].Value = item.Rate;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, IMPORT_LOANRATE, parms);
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public decimal getLoanRate(DateTime date, string symbol)
        {
            MySqlDataReader rdr = null;
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_TRANSDATE, MySqlDbType.DateTime),
                                                new MySqlParameter(PARM_SYMBOL, MySqlDbType.VarChar)};
                parms[0].Value = date;
                parms[1].Value = symbol;

                rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, GET_LOANRATE, parms);
                if (rdr.Read())
                    return Convert.ToDecimal(rdr["RATE"]);
                else
                    return 0;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }

        public void DeleteLoanRate(DateTime date)
        {
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_TRANSDATE, MySqlDbType.DateTime);
                parm1.Value = date;

                MyHelper.ExecuteNonQuerySingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, DELETE_LOANRATE, parm1);
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public void InsertMortgageTrans(string accountno, string contractno, DateTime date, string code, int type, decimal credit, decimal debit)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                        new MySqlParameter(PARM_ACCOUNTNO, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_CONTRACTNO, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_TRANSDATE, MySqlDbType.DateTime),
                                        new MySqlParameter(PARM_CODE, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_TYPE, MySqlDbType.Int32),
                                        new MySqlParameter(PARM_CREDIT, MySqlDbType.Decimal),
                                        new MySqlParameter(PARM_DEBIT, MySqlDbType.Decimal)};
                parms[0].Value = accountno;
                parms[1].Value = contractno;
                parms[2].Value = date;
                parms[3].Value = code;
                parms[4].Value = type;
                parms[5].Value = credit;
                parms[6].Value = debit;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, INSERT_MORTGATE_TRANS, parms);
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public void DeleteMortgageTransByContractNo(string contractno)
        {
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_CONTRACTNO, MySqlDbType.VarChar);
                parm1.Value = contractno;

                MyHelper.ExecuteNonQuerySingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, DELETE_MORTGATE_TRANS_BYCONTRACT, parm1);
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<MortgageTrans> getMortgageTransByCode(string contractno)
        {
            MySqlDataReader rdr = null;
            List<MortgageTrans> list = new List<MortgageTrans>();
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_CONTRACTNO, MySqlDbType.VarChar);
                parm1.Value = contractno;

                rdr = MyHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_MORTGAGE_TRANS_BYCODE, parm1);

                while (rdr.Read())
                {
                    list.Add(new MortgageTrans(rdr["CONTRACTNO"].ToString(),
                                                rdr["ACCOUNTNO"].ToString(),
                                                Convert.ToDateTime(rdr["TRANSDATE"]),
                                                rdr["CODE"].ToString(),
                                                Convert.ToInt32(rdr["TYPE"]),
                                                Convert.ToDecimal(rdr["DEBIT"]),
                                                Convert.ToDecimal(rdr["CREDIT"])));
                }
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }

        public List<MortgageTrans> searchMortgageTrans(string code, string accountno, string contractno,
                                                DateTime from, DateTime to, int type,
                                                decimal lowDebit, decimal highDebit,
                                                decimal lowCredit, decimal highCredit)
        {
            string STR_QUERY = SEARCH_TRANS;
            STR_QUERY += searchAccountNo(accountno) +
                            searchCode(code) +
                            searchContractNo(contractno) +
                            searchTransDate(from, to) +
                            searchDebit(lowDebit, highDebit) +
                            searchCredit(lowCredit, highCredit);
            STR_QUERY += " ORDER BY TransDate DESC";
            List<MortgageTrans> list = new List<MortgageTrans>();
            try
            {
                MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, STR_QUERY);
                while (rdr.Read())
                {
                    list.Add(new MortgageTrans(rdr["CONTRACTNO"].ToString(),
                                                rdr["ACCOUNTNO"].ToString(),
                                                Convert.ToDateTime(rdr["TRANSDATE"]),
                                                rdr["CODE"].ToString(),
                                                Convert.ToInt32(rdr["TYPE"]),
                                                Convert.ToDecimal(rdr["DEBIT"]),
                                                Convert.ToDecimal(rdr["CREDIT"])));

                }
                rdr.Close();
                return list;
            }
            catch
            {
                throw;
            }
        }





        public void InsertCustomer(string Name, string Gender, string AccountNo, string IdcardNo, DateTime IdcardDate, string IdcardIssue, string PhoneMobile, string PhoneFix, string Email, string AddressContact, string AddressResident)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                        new MySqlParameter(PARM_NAME, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_GENDER, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_ACCOUNTNO, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_IDCARDNO, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_IDCARDDATE, MySqlDbType.DateTime),
                                        new MySqlParameter(PARM_IDISSUER, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_PHONEMOBILE, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_PHONEFIX, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_EMAIL, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_ADDRESSCONTACT, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_ADDRESSRESIDENT, MySqlDbType.VarChar)};

                parms[0].Value = Name;
                parms[1].Value = Gender;
                parms[2].Value = AccountNo;
                parms[3].Value = IdcardNo;
                parms[4].Value = IdcardDate;
                parms[5].Value = IdcardIssue;
                parms[6].Value = PhoneMobile;
                parms[7].Value = PhoneFix;
                parms[8].Value = Email;
                parms[9].Value = AddressContact;
                parms[10].Value = AddressResident;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, INSERT_CUSTOMER, parms);

            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<AccountData> getCustomers()
        {
            MySqlDataReader rdr = null;
            List<AccountData> list = new List<AccountData>();
            try
            {
                rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_CUSTOMERS);

                while (rdr.Read())
                {
                    list.Add(new AccountData(rdr["NAME"].ToString(),
                                            rdr["GENDER"].ToString(),
                                            rdr["ACCOUNTNO"].ToString(),
                                            rdr["IDCARDNO"].ToString(),
                                            Convert.ToDateTime(rdr["IDCARDDATE"]),
                                            rdr["IDCARDISSUER"].ToString(),
                                            rdr["PHONEMOBILE"].ToString(),
                                            rdr["PHONEFIX"].ToString(),
                                            rdr["EMAIL"].ToString(),
                                            rdr["ADDRESSCONTACT"].ToString(),
                                            rdr["ADDRESSRESIDENT"].ToString()));
                }
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }

        public string getCustomerByAccountNo(string AccountNo)
        {
            MySqlDataReader rdr = null;
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_ACCOUNTNO, MySqlDbType.VarChar);
                parm1.Value = AccountNo;

                rdr = MyHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_CUSTOMERID_BYACCOUNTNO, parm1);

                if (rdr.Read())
                    return rdr["customerid"].ToString();
                else
                    return null;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }

        public AccountData getCustomerByID(string id)
        {
            MySqlDataReader rdr = null;
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_IDCARDNO, MySqlDbType.VarChar);
                parm1.Value = id;

                rdr = MyHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_CUSTOMER_BYID, parm1);

                if (rdr.Read())
                    return new AccountData(rdr["NAME"].ToString(),
                                            rdr["GENDER"].ToString(),
                                            rdr["ACCOUNTNO"].ToString(),
                                            rdr["IDCARDNO"].ToString(),
                                            Convert.ToDateTime(rdr["IDCARDDATE"]),
                                            rdr["IDCARDISSUER"].ToString(),
                                            rdr["PHONEMOBILE"].ToString(),
                                            rdr["PHONEFIX"].ToString(),
                                            rdr["EMAIL"].ToString(),
                                            rdr["ADDRESSCONTACT"].ToString(),
                                            rdr["ADDRESSRESIDENT"].ToString());
                else
                    return null;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }

        public void DeleteCustomerById(string CustomerID)
        {
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_IDCARDNO, MySqlDbType.VarChar);
                parm1.Value = CustomerID;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, DELETE_CUSTOMER_BYID, parm1);

            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public int InsertFee(DateTime startdate, DateTime enddate, string Name, int type, decimal feevalue, string customerid, string contractno, string accountno)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                        new MySqlParameter(PARM_STARTDATE, MySqlDbType.DateTime),
                                        new MySqlParameter(PARM_ENDDATE, MySqlDbType.DateTime),
                                        new MySqlParameter(PARM_NAME, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_TYPE, MySqlDbType.Int32),
                                        new MySqlParameter(PARM_FEEVALUE, MySqlDbType.Decimal),
                                        new MySqlParameter(PARM_CUSTOMERID, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_CONTRACTNO, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_ACCOUNTNO, MySqlDbType.VarChar)};

                parms[0].Value = startdate;
                parms[1].Value = enddate;
                parms[2].Value = Name;
                parms[3].Value = type;
                parms[4].Value = feevalue;
                parms[5].Value = customerid;
                parms[6].Value = contractno;
                parms[7].Value = accountno;

                return MyHelper.ExecuteScalar(_internalConnection,
                                                _internalADOTransaction,
                                                CommandType.Text, INSERT_FEE, parms);
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public FeeData getFeeByCode(string id)
        {
            MySqlDataReader rdr = null;
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_CUSTOMERID, MySqlDbType.Int32);
                parm1.Value = id;

                rdr = MyHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_FEE_BYCODE, parm1);

                if (rdr.Read())
                    return new FeeData(Convert.ToDateTime(rdr["STARTDATE"]),
                                            Convert.ToDateTime(rdr["ENDDATE"]),
                                            rdr["NAME"].ToString(),
                                            rdr["TYPE"].ToString(),
                                            Convert.ToDecimal(rdr["FEEVALUE"]),
                                            rdr["CONTRACTNO"].ToString(),
                                            rdr["CUSTOMERID"].ToString(),
                                            rdr["ACCOUNTNO"].ToString());
                else
                    return null;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }

        public FeeData getFeeByCodeAndDate(string Name, DateTime date)
        {
            MySqlDataReader rdr = null;
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                        new MySqlParameter(PARM_NAME, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_TRANSDATE, MySqlDbType.DateTime)};

                parms[0].Value = Name;
                parms[1].Value = date;

                rdr = MyHelper.ExecuteReaderSingleRow(_internalConnection, _internalADOTransaction, CommandType.Text, GET_CUSTOMER_BYID, parms);

                if (rdr.Read())
                    return new FeeData(Convert.ToDateTime(rdr["STARTDATE"]),
                                            Convert.ToDateTime(rdr["ENDDATE"]),
                                            rdr["NAME"].ToString(),
                                            rdr["TYPE"].ToString(),
                                            Convert.ToDecimal(rdr["FEEVALUE"]),
                                            rdr["CONTRACTNO"].ToString(),
                                            rdr["CUSTOMERID"].ToString(),
                                            rdr["ACCOUNTNO"].ToString());
                else
                    return null;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }

        public FeeData getFee(int type, string customerid, string contractno, string accountno)
        {
            MySqlDataReader rdr = null;
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                        new MySqlParameter(PARM_TYPE, MySqlDbType.Int32),
                                        new MySqlParameter(PARM_CUSTOMERID, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_CONTRACTNO, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_ACCOUNTNO, MySqlDbType.VarChar)};

                parms[0].Value = type;
                parms[1].Value = customerid;
                parms[2].Value = contractno;
                parms[3].Value = accountno;

                rdr = MyHelper.ExecuteReaderSingleRow(_internalConnection, _internalADOTransaction, CommandType.Text, GET_FEE, parms);

                if (rdr.Read())
                    return new FeeData(Convert.ToDateTime(rdr["STARTDATE"]),
                                            Convert.ToDateTime(rdr["ENDDATE"]),
                                            rdr["NAME"].ToString(),
                                            rdr["TYPE"].ToString(),
                                            Convert.ToDecimal(rdr["FEEVALUE"]),
                                            rdr["CONTRACTNO"].ToString(),
                                            rdr["CUSTOMERID"].ToString(),
                                            rdr["ACCOUNTNO"].ToString());
                else
                    return null;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }

        public void InsertContract(string contractno, string customerid, DateTime start, DateTime end, decimal fee, decimal penaltyfee, decimal processfee, int contracttype, string status, decimal contractvalue, string AccountNo, string Oringal)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                        new MySqlParameter(PARM_CONTRACTNO, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_CUSTOMERID, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_STARTDATE, MySqlDbType.DateTime),
                                        new MySqlParameter(PARM_ENDDATE, MySqlDbType.DateTime),
                                        new MySqlParameter(PARM_FEE, MySqlDbType.Decimal),
                                        new MySqlParameter(PARM_PENALTYFEE, MySqlDbType.Decimal),
                                        new MySqlParameter(PARM_PROCESSFEE, MySqlDbType.Decimal),
                                        new MySqlParameter(PARM_CONTRACTTYPE, MySqlDbType.Int32),
                                        new MySqlParameter(PARM_STATUS, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_CONTRACTVALUE, MySqlDbType.Decimal),
                                        new MySqlParameter(PARM_ACCOUNTNO, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_ORIGINAL, MySqlDbType.VarChar)};

                parms[0].Value = contractno;
                parms[1].Value = customerid;
                parms[2].Value = start;
                parms[3].Value = end;
                parms[4].Value = fee;
                parms[5].Value = penaltyfee;
                parms[6].Value = processfee;
                parms[7].Value = contracttype;
                parms[8].Value = status;
                parms[9].Value = contractvalue;
                parms[10].Value = AccountNo;
                parms[11].Value = Oringal;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, INSERT_CONTRACT, parms);

            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public ContractData getContractByNo(string contractno)
        {
            MySqlDataReader rdr = null;
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_CONTRACTNO, MySqlDbType.VarChar);
                parm1.Value = contractno;

                rdr = MyHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_CONTRACT_BYCONTRACTNO, parm1);

                if (rdr.Read())
                    return new ContractData(rdr["CONTRACTNO"].ToString(),
                                            rdr["CUSTOMERID"].ToString(),
                                            Convert.ToDateTime(rdr["STARTDATE"]),
                                            Convert.ToDateTime(rdr["ENDDATE"]),
                                            Convert.ToDecimal(rdr["CONTRACTVALUE"]),
                                            Convert.ToDecimal(rdr["FEE"]),
                                            Convert.ToDecimal(rdr["PENALTYFEE"]),
                                            Convert.ToDecimal(rdr["PROCESSFEE"]),
                                            Convert.ToInt32(rdr["CONTRACTTYPE"]),
                                            rdr["ORIGINAL"].ToString(),
                                            rdr["ACCOUNTNO"].ToString());
                else
                    return null;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }

        public List<ContractData> getcontractByCustomerId(string customerid)
        {
            MySqlDataReader rdr = null;
            List<ContractData> list = new List<ContractData>();
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_CONTRACTNO, MySqlDbType.VarChar);
                parm1.Value = customerid;

                rdr = MyHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_CONTRACT_BYCUSTOMERID, parm1);

                while (rdr.Read())
                {
                    list.Add(new ContractData(rdr["CONTRACTNO"].ToString(),
                                            rdr["CUSTOMERID"].ToString(),
                                            Convert.ToDateTime(rdr["STARTDATE"]),
                                            Convert.ToDateTime(rdr["ENDDATE"]),
                                            Convert.ToDecimal(rdr["CONTRACTVALUE"]),
                                            Convert.ToDecimal(rdr["FEE"]),
                                            Convert.ToDecimal(rdr["PENALTYFEE"]),
                                            Convert.ToDecimal(rdr["PROCESSFEE"]),
                                            Convert.ToInt32(rdr["CONTRACTTYPE"]),
                                            rdr["ORIGINAL"].ToString(),
                                            rdr["ACCOUNTNO"].ToString()));
                }
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }

        public List<ContractData> getContractByStartDate(DateTime from, DateTime to)
        {
            MySqlDataReader rdr = null;
            List<ContractData> list = new List<ContractData>();
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                        new MySqlParameter(PARM_FROM, MySqlDbType.DateTime),
                                        new MySqlParameter(PARM_TO, MySqlDbType.DateTime)};

                parms[0].Value = from;
                parms[1].Value = to;

                rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, GET_CONTRACT_BYSTARTDATE, parms);

                while (rdr.Read())
                {
                    list.Add(new ContractData(rdr["CONTRACTNO"].ToString(),
                                            rdr["CUSTOMERID"].ToString(),
                                            Convert.ToDateTime(rdr["STARTDATE"]),
                                            Convert.ToDateTime(rdr["ENDDATE"]),
                                            Convert.ToDecimal(rdr["CONTRACTVALUE"]),
                                            Convert.ToInt32(rdr["FEE"]),
                                            Convert.ToInt32(rdr["PENALTYFEE"]),
                                            Convert.ToInt32(rdr["PROCESSFEE"]),
                                            Convert.ToInt32(rdr["CONTRACTTYPE"]),
                                            rdr["ORIGINAL"].ToString(),
                                            rdr["ACCOUNTNO"].ToString()));
                }
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }

        public List<ContractData> getContractByEndDate(DateTime from, DateTime to)
        {
            MySqlDataReader rdr = null;
            List<ContractData> list = new List<ContractData>();
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                        new MySqlParameter(PARM_FROM, MySqlDbType.DateTime),
                                        new MySqlParameter(PARM_TO, MySqlDbType.DateTime)};

                parms[0].Value = from;
                parms[1].Value = to;

                rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, GET_CONTRACT_BYENDDATE, parms);

                while (rdr.Read())
                {
                    list.Add(new ContractData(rdr["CONTRACTNO"].ToString(),
                                            rdr["CUSTOMERID"].ToString(),
                                            Convert.ToDateTime(rdr["STARTDATE"]),
                                            Convert.ToDateTime(rdr["ENDDATE"]),
                                            Convert.ToDecimal(rdr["CONTRACTVALUE"]),
                                            Convert.ToDecimal(rdr["FEE"]),
                                            Convert.ToDecimal(rdr["PENALTYFEE"]),
                                            Convert.ToDecimal(rdr["PROCESSFEE"]),
                                            Convert.ToInt32(rdr["CONTRACTTYPE"]),
                                            rdr["ORIGINAL"].ToString(),
                                            rdr["ACCOUNTNO"].ToString()));
                }
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }

        public List<ContractData> getContractByValue(decimal from, decimal to)
        {
            MySqlDataReader rdr = null;
            List<ContractData> list = new List<ContractData>();
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                        new MySqlParameter(PARM_FROM, MySqlDbType.Decimal),
                                        new MySqlParameter(PARM_TO, MySqlDbType.Decimal)};

                parms[0].Value = from;
                parms[1].Value = to;

                rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, GET_CONTRACT_BYVALUE, parms);

                while (rdr.Read())
                {
                    list.Add(new ContractData(rdr["CONTRACTNO"].ToString(),
                                            rdr["CUSTOMERID"].ToString(),
                                            Convert.ToDateTime(rdr["STARTDATE"]),
                                            Convert.ToDateTime(rdr["ENDDATE"]),
                                            Convert.ToDecimal(rdr["CONTRACTVALUE"]),
                                            Convert.ToDecimal(rdr["FEE"]),
                                            Convert.ToDecimal(rdr["PENALTYFEE"]),
                                            Convert.ToDecimal(rdr["PROCESSFEE"]),
                                            Convert.ToInt32(rdr["CONTRACTTYPE"]),
                                            rdr["ORIGINAL"].ToString(),
                                            rdr["ACCOUNTNO"].ToString()));
                }
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }

        public List<ContractData> searchContract(string contractno, string customerid,
                                            DateTime from, DateTime to,
                                            decimal lowvalue, decimal highvalue,
                                            int contracttype, string status, string AccountNo,
                                            string originalContract)
        {
            string STR_QUERY = SEARCH_CONTRACT;
            STR_QUERY += searchAccountNo(AccountNo) + searchCustomerID(customerid)
                        + searchContractNo(contractno) +
                        searchDate(from, to) + searchValue(lowvalue, highvalue) +
                        searchType(contracttype) + searchStatus(status) + 
                        searchOriginalContractNo(originalContract);
            STR_QUERY += " ORDER BY StartDate DESC";
            List<ContractData> list = new List<ContractData>();
            try
            {
                MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, STR_QUERY);
                while (rdr.Read())
                {
                    list.Add(new ContractData(rdr["CONTRACTNO"].ToString(),
                                            rdr["CUSTOMERID"].ToString(),
                                            Convert.ToDateTime(rdr["STARTDATE"]),
                                            Convert.ToDateTime(rdr["ENDDATE"]),
                                            Convert.ToDecimal(rdr["CONTRACTVALUE"]),
                                            Convert.ToDecimal(rdr["FEE"]),
                                            Convert.ToDecimal(rdr["PENALTYFEE"]),
                                            Convert.ToDecimal(rdr["PROCESSFEE"]),
                                            Convert.ToInt32(rdr["CONTRACTTYPE"]),
                                            rdr["ORIGINAL"].ToString(),
                                            rdr["ACCOUNTNO"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch
            {
                throw;
            }
        }

        public void InsertCustomerAccount(string customerId, string accounts)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                        new MySqlParameter(PARM_CUSTOMERID, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_ACCOUNTNO, MySqlDbType.VarChar)};

                parms[0].Value = customerId;
                parms[1].Value = accounts;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, INSERT_CUSTOMERACCOUNT, parms);

            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<string> getCustomerAccounts(string customerId)
        {
            List<string> list = new List<string>();

            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_CUSTOMERID, MySqlDbType.VarChar);
                parm1.Value = customerId;

                MySqlDataReader rdr = MyHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_CUSTOMERACCOUNT_BYCUSTOMERID, parm1);

                while (rdr.Read())
                {
                    list.Add(rdr["ACCOUNTNO"].ToString());
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public void deleteCustomerAccount(string customerId)
        {
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_CUSTOMERID, MySqlDbType.VarChar);
                parm1.Value = customerId;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, DELETE_CUSTOMERACCOUNT_BYCUSTOMERID, parm1);

            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public decimal getBlockingQttyByCodeAndContract(string AccountNo, string code, string contractno)
        {
            MySqlDataReader rdr = null;
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                        new MySqlParameter(PARM_ACCOUNTNO, MySqlDbType.DateTime),
                                        new MySqlParameter(PARM_CODE, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_CONTRACTNO, MySqlDbType.VarChar)};

                parms[0].Value = AccountNo;
                parms[1].Value = code;
                parms[2].Value = contractno;

                rdr = MyHelper.ExecuteReaderSingleRow(_internalConnection, _internalADOTransaction, CommandType.Text, GET_BLOCKING_BYCODEANDCONTRACTNO, parms);

                if (rdr.Read())
                    return Convert.ToDecimal(rdr["QTTY"]);
                else
                    return 0;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }

        public decimal getBlockingQttyByCode(string AccountNo, string code)
        {
            MySqlDataReader rdr = null;
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                        new MySqlParameter(PARM_ACCOUNTNO, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_CODE, MySqlDbType.VarChar)};

                parms[0].Value = AccountNo;
                parms[1].Value = code;

                rdr = MyHelper.ExecuteReaderSingleRow(_internalConnection, _internalADOTransaction, CommandType.Text, GET_BLOCKING_BYCODE, parms);

                if (rdr.Read())
                    return (rdr["QTTY"] is DBNull) ? 0 : Convert.ToDecimal(rdr["QTTY"]);
                else
                    return 0;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }

        public List<ContractData> getContractByAccountNo(string accountno)
        {
            throw new NotImplementedException();
        }

        private string searchContractNo(string ContractNo)
        {
            if (string.IsNullOrEmpty(ContractNo))
                return String.Empty;
            else
                return " AND ContractNo='" + ContractNo.Trim() + "'";
        }
        private string searchOriginalContractNo(string originalContract)
        {
            if (string.IsNullOrEmpty(originalContract))
                return String.Empty;
            else
                return " AND Original='" + originalContract.Trim() + "'";
        }

        private string searchAccountNo(string AccountNo)
        {
            if (string.IsNullOrEmpty(AccountNo))
                return String.Empty;
            else
                return " AND AccountNo LIKE '%" + AccountNo.Trim() + "%'";
        }
        private string searchCustomerID(string CustomerID)
        {
            if (string.IsNullOrEmpty(CustomerID))
                return String.Empty;
            else
                return " AND CustomerID='" + CustomerID.Trim() + "'";
        }
        private string searchType(int type)
        {
            if (type.Equals(-1))
                return String.Empty;
            else
                return " AND contracttype='" + type.ToString() + "'";
        }
        private string searchStatus(string status)
        {
            if (string.IsNullOrEmpty(status))
                return String.Empty;
            else
                return " AND status='" + status.ToString() + "'";
        }
        private string searchDate(DateTime from, DateTime to)
        {
            if (from.Equals(to))
                return String.Empty;
            else
                return " AND (startdate>='" + from.ToString("yyyy/MM/dd") +
                        "') AND (enddate <='" + to.ToString("yyyy/MM/dd") + "')";
        }
        private string searchValue(decimal LowerPrice, decimal HigherPrice)
        {
            if (LowerPrice.Equals(HigherPrice))
                if (HigherPrice.Equals(0))
                    return String.Empty;
                else
                    return " AND contractvalue='" + LowerPrice.ToString() + "'";
            else
                return " AND (contractvalue>='" + LowerPrice.ToString()
                        + "') AND (contractvalue <='" + HigherPrice.ToString() + "')";
        }
        private string searchCode(string Code)
        {
            if (string.IsNullOrEmpty(Code))
                return String.Empty;
            else
                return " AND Code='" + Code.Trim() + "'";
        }
        private string searchTransDate(DateTime from, DateTime to)
        {
            if (from.Equals(to))
                return String.Empty;
            else
                return " AND (TransDate>='" + from.ToString("yyyy/MM/dd") +
                        "') AND (TransDate <='" + to.ToString("yyyy/MM/dd") + "')";
        }
        private string searchCredit(decimal from, decimal to)
        {
            if (from.Equals(to))
                if (from.Equals(0))
                    return String.Empty;
                else
                    return " AND Credit=@" + from.ToString();
            else
                return " AND (Credit>='" + from.ToString() +
                        "') AND (Credit <='" + to.ToString() + "')";
        }
        private string searchDebit(decimal from, decimal to)
        {
            if (from.Equals(to))
                if (from.Equals(0))
                    return String.Empty;
                else
                    return " AND Debit=@" + from.ToString();
            else
                return " AND (Debit>='" + from.ToString() +
                        "') AND (Debit <='" + to.ToString() + "')";
        }

        public void UpdateAccumulateLoan(string AccountNo, decimal AccuValue, DateTime LastUpdate)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                        new MySqlParameter(PARM_ACCOUNTNO, MySqlDbType.VarChar),
                                        new MySqlParameter("@ACCUMULATEVALUE", MySqlDbType.VarChar),
                                        new MySqlParameter("@LASTUPDATE", MySqlDbType.Timestamp)};

                parms[0].Value = AccountNo;
                parms[1].Value = AccuValue;
                parms[2].Value = LastUpdate;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, INSERT_ACCUMULATELOAN, parms);

            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public decimal GetAccumulateLoanByAccountNo(string AccountNo)
        {
            MySqlDataReader rdr = null;
            try
            {
                MySqlParameter parms =  new MySqlParameter(PARM_ACCOUNTNO, MySqlDbType.VarChar);
                parms.Value = AccountNo;

                rdr = MyHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_ACCUMULATEVALUE_BYACCOUNTNO, parms);

                if (rdr.Read())
                    return (rdr["ACCUMULATEVALUE"] is DBNull) ? 0 : Convert.ToDecimal(rdr["ACCUMULATEVALUE"]);
                else
                    return 0;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }
        #endregion
    }
}
